package dao;

import org.apache.commons.beanutils.BeanUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据库update和query操作类
 * Created by jcala on 2016/3/15.
 */
public class UpdateQuery {
    /**
     *
     * @param sql 要执行的增删改的sql语句
     * @param args PreparedStatement的执行参数
     * @return 操作失败返回0，成功就返回1
     */
    protected int update(String sql, Object... args) {
        int status=1;
        Connection conn = JDBCTools.getConn();
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            ps.executeUpdate();
        } catch (SQLException e) {
            status=0;
            e.printStackTrace();
        } finally {
            JDBCTools.releaseConn(conn, ps, null);
        }
        return status;
    }

    /**
     *查询操作，获取符合查询条件的结果数量
     * @param sql 要执行的增删改的sql语句
     * @param args PreparedStatement的执行参数
     * @return
     */
    protected int getNum(String sql, Object... args){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int num=0;
        try {
            conn = JDBCTools.getConn();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            while(rs.next()){num++;}
        } catch (SQLException e) {
            num=-1;
        } finally {
            JDBCTools.releaseConn(conn,ps,null);
        }
        return num;
    }

    /**
     *查询第一个符合查询条件的行，并生成相应javabean
     * @param clazz 要返回的javabean对应的类型，用反射实现
     * @param sql 要执行的增删改的sql语句
     * @param args PreparedStatement的执行参数
     * @return 根据查询到的符合条件的第一行生成的javabean
     * @throws Exception
     */
    protected <T> T get(Class<T> clazz, String sql, Object... args)
            throws Exception {
        T entity = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = JDBCTools.getConn();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            Map<String, Object> map = null;

            while (rs.next()) {
                map = new HashMap<String, Object>();
                for (int i = 0; i < rsmd.getColumnCount(); i++) {
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    Object columnValue = rs.getObject(i + 1);
                    map.put(columnLabel, columnValue);
                }
                if (map.size() > 0) {
                    entity = clazz.newInstance();
                    for (Map.Entry<String, Object> entry : map.entrySet()) {
                        String label = entry.getKey();
                        Object value = entry.getValue();
                        BeanUtils.setProperty(entity, label, value);
                    }
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCTools.releaseConn(conn,ps,rs);
        }
        return entity;
    }

    /**
     * 查询所有符合查询条件的行，并生成相应javabean
     * @param clazz 要返回的javabean对应的类型，用反射实现
     * @param sql 要执行的增删改的sql语句
     * @param args PreparedStatement的执行参数
     * @return
     * @throws Exception
     */
    protected <T> List<T> getForList(Class<T> clazz, String sql, Object... args)
            throws Exception {
        T entity = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<T> result = new ArrayList<T>();

        try {
            conn = JDBCTools.getConn();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();

            ResultSetMetaData rsmd = rs.getMetaData();
            List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
            Map<String, Object> map = new HashMap<String, Object>();

            while (rs.next()) {
                for (int i = 0; i < rsmd.getColumnCount(); i++) {
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    Object columnValue = rs.getObject(i + 1);
                    map.put(columnLabel, columnValue);
                }
                list.add(map);
                if (list.size() > 0) {
                    entity = clazz.newInstance();
                    for (Map<String, Object> ll : list) {
                        for (Map.Entry<String, Object> entry : ll.entrySet()) {
                            String label = entry.getKey();
                            Object value = entry.getValue();
                            BeanUtils.setProperty(entity, label, value);
                        }
                    }
                    result.add(entity);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCTools.releaseConn(conn,ps,rs);
        }
        return result;
    }

    /**
     * 查询符合查询条件的第一行的的某列
     * @param sql 要执行的增删改的sql语句
     * @param column 要查询的列数
     * @param args PreparedStatement的执行参数
     * @param <E> 查询到的某列的值的类型
     * @return 查询到的某列的值
     * @throws Exception
     */
    protected <E> E getforvalue(String sql,int column, Object... args) throws Exception {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCTools.getConn();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            if (rs.next()) {
                return (E) rs.getObject(column);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCTools.releaseConn(conn,ps,null);
        }
        return null;
    }
}
